import pymysql
from typing import List, Optional
from src.domain.email_pending_task import EmailPendingTask
from src.domain.email_pending_task_repository import EmailPendingTaskRepository
from src.infrastructure.mysql_connection import MySQLConnection

class MySQLEmailPendingTaskRepository(EmailPendingTaskRepository):
    """MySQL implementation of EmailPendingTaskRepository"""
    
    def __init__(self, mysql_connection: MySQLConnection):
        self.mysql_connection = mysql_connection
    
    def save(self, email_pending_task: EmailPendingTask) -> int:
        """Save an email pending task and return the ID"""
        query = """
        INSERT INTO email_pending_task 
        (email_id, task_id, email_info_json, sop_json, generated_reply, action_type, created_by)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        
        try:
            with self.mysql_connection.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, (
                        email_pending_task.email_id,
                        email_pending_task.task_id,
                        email_pending_task.email_info_json,
                        email_pending_task.sop_json,
                        email_pending_task.generated_reply,
                        email_pending_task.action_type,
                        email_pending_task.created_by
                    ))
                    conn.commit()
                    return cursor.lastrowid
        except pymysql.Error as e:
            raise Exception(f"Failed to save email pending task: {e}")
    
    def find_by_id(self, task_id: int) -> Optional[EmailPendingTask]:
        """Find an email pending task by ID"""
        query = "SELECT * FROM email_pending_task WHERE id = %s"
        
        try:
            with self.mysql_connection.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, (task_id,))
                    result = cursor.fetchone()
                    if result:
                        return self._row_to_entity(result)
                    return None
        except pymysql.Error as e:
            raise Exception(f"Failed to find email pending task by ID: {e}")
    
    def find_by_email_id(self, email_id: str) -> List[EmailPendingTask]:
        """Find email pending tasks by email_id"""
        query = "SELECT * FROM email_pending_task WHERE email_id = %s ORDER BY created_at DESC"
        
        try:
            with self.mysql_connection.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, (email_id,))
                    results = cursor.fetchall()
                    return [self._row_to_entity(row) for row in results]
        except pymysql.Error as e:
            raise Exception(f"Failed to find email pending tasks by email_id: {e}")
    
    def find_by_task_id(self, task_id: str) -> Optional[EmailPendingTask]:
        """Find an email pending task by task_id"""
        query = "SELECT * FROM email_pending_task WHERE task_id = %s"
        
        try:
            with self.mysql_connection.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, (task_id,))
                    result = cursor.fetchone()
                    if result:
                        return self._row_to_entity(result)
                    return None
        except pymysql.Error as e:
            raise Exception(f"Failed to find email pending task by task_id: {e}")
    
    def _row_to_entity(self, row: dict) -> EmailPendingTask:
        """Convert database row to EmailPendingTask entity"""
        return EmailPendingTask(
            id=row['id'],
            email_id=row['email_id'],
            task_id=row['task_id'],
            email_info_json=row['email_info_json'],
            sop_json=row['sop_json'],
            generated_reply=row['generated_reply'],
            action_type=row['action_type'],
            created_at=row['created_at'],
            created_by=row['created_by']
        )